<?php

namespace Budget\BudgetBundle\Repository;

use Doctrine\ORM\EntityRepository;
use Budget\BudgetBundle\Entity\Family;
use Budget\BudgetBundle\Entity\User;
use Budget\BudgetBundle\Entity\Category;
use Symfony\Component\HttpFoundation\Request;
use Budget\BudgetBundle\Helper\DateHelper;
use Budget\BudgetBundle\Controller\MainController;
use Budget\BudgetBundle\Helper\FamilyHelper;
use Budget\BudgetBundle\Entity\FamilyMonth;


class ExpenseRepository extends EntityRepository
{
    public function findByIdForFamily(Family $family, $id)
    {
        $qb = $this->createQueryBuilder('e')
            ->select('e')
            ->leftJoin('e.category', 'c')
            ->where('e.id = :id')
            ->andWhere('c.family = :familyId')
            ->andWhere('e.date_deleted IS NULL')
            ->andWhere('c.date_deleted IS NULL')
            ->setParameter('familyId', $family->getId())
            ->setParameter('id', $id);

        return $qb->getQuery()->getOneOrNullResult();
    }

    public function getQuerySearch($requestArray, User $user)
    {
        $where           = array();
        $whereParameters = array();
        for ($i = 0; $i < count($requestArray); $i++) {
            $k = $requestArray[$i]['name'];
            $v = $requestArray[$i]['value'];
            if ($k[1] == '_') {
                $k[1] = '.';
            }

            switch ($k) {
                case 'e.id':
                case 'c.name':
                    //$where .= " AND ".$k." LIKE '%".$v."%'";
                    $where[]           = "c.name LIKE :name";
                    $whereParameters[] = array('name' => ':name', 'val' => '%' . $v . '%');
                    break;
                case 'e.price':
                    //$where .= " AND ".$k." LIKE '%".$v."%'";
                    $where[]           = "e.price LIKE :price";
                    $whereParameters[] = array('name' => ':price', 'val' => '%' . $v . '%');
                    break;
                case 'e.comment':
                    //$where .= " AND ".$k." LIKE '%".$v."%'";
                    $where[]           = "e.comment LIKE :comment";
                    $whereParameters[] = array('name' => ':comment', 'val' => '%' . $v . '%');
                    break;
                case 'categories':
                    $idCategories     = array();
                    $categoriesResult = $this->getEntityManager()->getRepository('BudgetBudgetBundle:Category')->findIdCategoriesForFamily($user->getFamily(), $v);
                    if (count($categoriesResult) > 0) {
                        for ($j = 0; $j < count($categoriesResult); $j++) {
                            $idCategories[] = $categoriesResult[$j]['id'];
                        }
                    }
                    //if (count($idCategories) > 0) {  
                    if ($idCategories[0]) {
                        $where[]           = "e.category IN (:categories)";
                        $v                 = implode(', ', $idCategories);
                        $whereParameters[] = array('name' => ':categories', 'val' => array_values($idCategories));
                    }
                    break;
                case 'date':
                case 'e.date':
                    $year         = DateHelper::getYear($v);
                    $month        = DateHelper::getMonth($v);
                    $startDateStr = FamilyHelper::getStartMonthDbStr($user->getFamily(), $year, $month);
                    $endDateStr   = FamilyHelper::getEndMonthDbStr($user->getFamily(), $year, $month);

                    $where[]           = "e.date >= :dateFrom";
                    $where[]           = "e.date <= :dateTo";
                    $whereParameters[] = array('name' => ':dateFrom', 'val' => $startDateStr);
                    $whereParameters[] = array('name' => ':dateTo', 'val' => $endDateStr);
                    break;
                case 'month':
                    //$where .= " AND e.date_created >= '".$v."-01 00:00:00' AND e.date_created <= '".$v."-31 24:59:59'";          
                    $where[]           = "e.date <= :dateTo";
                    $where[]           = "e.date >= :dateFrom";
                    $whereParameters[] = array('name' => ':dateTo', 'val' => $v . '-31 23:59:59');
                    $whereParameters[] = array('name' => ':dateFrom', 'val' => $v . '-01 00:00:00');
                    break;
                    break;
            }
        }

        return array('where' => $where, 'parameters' => $whereParameters);
    }

    public function getExpensesForFamilyToJson(Family $family, $page, $limit, $sidx, $sord, $where)
    {
        $expenses = $this->get('budget_expense')->getExpensesForFamilyToArray($family, $page, $limit, $sidx, $sord, $where);
        $response = $this->get('budget_expense')->getFormatExpensesToJson($expenses);
        return $response;
    }

    public function checkActiveExpense(Category $category)
    {
        $qb     = $this->createQueryBuilder('e')
            ->select('e')
            ->where('e.category = :category')
            ->andWhere('e.date_deleted IS NULL')
            ->setParameter('category', $category->getId());
        $result = $qb->getQuery()->getArrayResult();
        return count($result);
    }

    public function getTotalSumExpensesForFamilyQuery($family, $dateFrom, $dateTo)
    {
        $qb = $this->createQueryBuilder('e')
            ->select('SUM(e.price) as total')
            ->leftJoin('e.category', 'c')
            ->where('c.family = :family')
            ->andWhere('e.date_deleted IS NULL')
            ->andWhere('c.date_deleted IS NULL')
            ->andWhere('e.date >= :dateFrom ')
            ->andWhere('e.date <= :dateTo ')
            ->setParameter('family', $family->getId())
            ->setParameter('dateFrom', $dateFrom)
            ->setParameter('dateTo', $dateTo);
        return $qb->getQuery()->getArrayResult();
    }

    public function getExpensesForFamilyToArrayQuery($family, $sidx, $sord, $whereSQL)
    {
        $qb = $this->createQueryBuilder('e')
            ->select('e', 'c')
            ->leftJoin('e.category', 'c')
            ->where('c.family = :family')
            ->andWhere('e.date_deleted IS NULL')
            ->andWhere('c.date_deleted IS NULL' . $whereSQL)
            ->addOrderBy($sidx, $sord)
            ->setParameter('family', $family->getId());
        return $qb;
    }

    public function getExpensesSumForFamilyMonth(FamilyMonth $familyMonth, $countSavingCategories = false)
    {
        $qb = $this->createQueryBuilder('e')
            ->select('SUM(e.price)')
            ->join('e.category', 'c')
            ->where('e.date >= :start')
            ->andWhere('e.date <= :end')
            ->andWhere('e.date_deleted IS NULL')
            ->andWhere('c.family = :familyId')
            ->setParameters(array(
                'start'    => $familyMonth->getStart()->format(DateHelper::FORMAT_DATE_DB),
                'end'      => $familyMonth->getEnd()->format(DateHelper::FORMAT_DATE_DB),
                'familyId' => $familyMonth->getFamily()->getId(),
            ));

        if ($countSavingCategories) {
            $qb->andWhere('c.isSaver = :saver')
                ->setParameter('saver', 1);
        } else {
            $qb->andWhere('c.isSaver IS NULL OR c.isSaver = 0');
        }

        $result = $qb->getQuery()->getSingleScalarResult();

        if (is_null($result)) {
            return 0;
        }

        return $result;
    }

    /**
     * @param Family $family
     * @param null $data
     * @return array
     */
    public function countByPeriodForFamily(Family $family, $data = null)
    {
        $avgNumber  = 1;
        $firstMonth = null;
        $lastMonth  = null;

        if (isset($data['year']) && isset($data['month'])) {
            $familyMonths = $this->getEntityManager()
                ->getRepository('BudgetBudgetBundle:FamilyMonth')
                ->findBy(array('year' => $data['year'], 'month' => $data['month']), array('month' => 'ASC'));
            $avgNumber    = count($familyMonths);
            $firstMonth   = current($familyMonths);
            $lastMonth    = end($familyMonths);
        }

        if (isset($data['type']) && $data['type'] == 'sum') {
            $avgNumber = 1;
        }

        $qb = $this->createQueryBuilder('e')
            ->select('SUM(e.price)/ ' . $avgNumber . ' as price', 'c.id', 'c.name')
            ->leftJoin('e.category', 'c')
            ->where('e.date_deleted IS NULL')
            ->andWhere('c.family = :family')
            ->setParameters(array(
                'family' => $family->getId()
            ))
            ->groupBy('c.id, c.name')
            ->orderBy('price', 'DESC');

        if (!is_null($firstMonth)) {
            $qb->andWhere('e.date >= :startDate')
                ->setParameter('startDate', $firstMonth->getStart());
        }

        if (!is_null($lastMonth)) {
            $qb->andWhere('e.date <= :endDate')
                ->setParameter('endDate', $lastMonth->getEnd());
        }

        return $qb->getQuery()->getResult();
    }

    public function getAllByCategory(Category $category, $data = null)
    {
        $qb = $this->createQueryBuilder('e')
            ->select('e')
            ->innerJoin('e.category', 'c')
            ->where('e.date_deleted IS NULL')
            ->andWhere('c.family = :family')
            ->andWhere('e.category = :category')
            ->setParameters(array(
                'family'   => $category->getFamily()->getId(),
                'category' => $category->getId()
            ))
            ->orderBy('e.price', 'DESC');

        if (isset($data['year']) && isset($data['months'])) {
            $familyMonths = $this->getEntityManager()
                ->getRepository('BudgetBudgetBundle:FamilyMonth')
                ->findByParamsForFamily($category->getFamily(), $data);

            $orX = $qb->expr()->orX();
            foreach ($familyMonths as $familyMonth) {
                $part = $familyMonth->getId();
                $orX->add('e.date BETWEEN :startDate'.$part.' AND :endDate'.$part);
                $qb->setParameter('startDate'.$part, $familyMonth->getStart())
                    ->setParameter('endDate'.$part, $familyMonth->getEnd());

            }
            $qb->andWhere($orX);

        }

        return $qb->getQuery()->getArrayResult();

    }
}
